"""
类方法：
get_conn(cls): 建立连接，获取连接对象
query_one(cls, sql): 查询一条数据
query_all(cls, sql): 查询全部数据
iud_data(cls, sql): 执行数据库增删改
"""
import pymysql


class DBUtil(object):
    @classmethod
    def get_conn(cls):
        return pymysql.connect(host='211.103.136.244', port=7061, user='student', password='iHRM_student_2021',
                               database='test_db', charset='utf8')

    @classmethod
    def close_cons(cls, cursor, connect):
        cursor.close()
        connect.close()

    @classmethod
    def query_one(cls, sql):
        '''
        查询一条数据
        :param sql:
        :return: 返回一条数据
        '''
        connect = cls.get_conn()
        cursor = connect.cursor()
        num = cursor.execute(sql)
        print('Affected rows: ', num)

        res = cursor.fetchone()

        cls.close_cons(cursor, connect)

        return res

    @classmethod
    def query_all(cls, sql):
        '''
        查询所有数据
        :param sql:
        :return:返回所有数据
        '''
        connect = cls.get_conn()
        cursor = connect.cursor()
        num = cursor.execute(sql)
        print('Affected rows: ', num)

        res = cursor.fetchall()

        cls.close_cons(cursor, connect)

        return res

    @classmethod
    def dml_data(cls, sql):
        '''
        更新、删除、修改语句
        :param sql:
        :return:
        '''
        connect = cls.get_conn()
        cursor = connect.cursor()
        num = None

        try:
            num = cursor.execute(sql)
        except Exception as e:
            print('Affected rows: ', num)
            print('执行错误：', e)
            connect.rollback()
        else:
            connect.commit()
            print('Affected rows: ', num)
        finally:
            cls.close_cons(cursor, connect)


if __name__ == '__main__':
    sql = 'select * from t_book'

    # 获取一条数据
    print(DBUtil.query_one(sql))

    # 获取全部数据
    print(DBUtil.query_all(sql))

    insert_sql = "INSERT INTO t_book(title,pub_date,`read`,`comment`,is_delete) VALUES('回手掏','1999-01-01',100,200,0)"
    DBUtil.dml_data(insert_sql)

    update_sql = "UPDATE t_book SET `read` = `read` + 1 WHERE title = '回手掏'"
    DBUtil.dml_data(update_sql)

    delete_sql = "DELETE FROM t_book WHERE title = '回手掏1'"
    DBUtil.dml_data(delete_sql)
